package com.geline.sqlbackup.util;

import cn.hutool.core.util.StrUtil;
import cn.hutool.db.DbUtil;
import cn.hutool.db.Entity;
import cn.hutool.db.sql.SqlExecutor;
import cn.hutool.extra.spring.SpringUtil;
import com.alibaba.druid.DbType;
import com.alibaba.druid.sql.PagerUtils;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import lombok.extern.slf4j.Slf4j;
import org.springframework.jdbc.datasource.DataSourceUtils;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.*;
import java.util.stream.Collectors;

/**
 * sql-CRUD操作工具类
 * @author: mengx
 * @date: 2021/8/14 16:09
 */
@Slf4j
public class SqlRunnerUtils {

    public static List<Map<String, Object>> selectMaps(String sql){
        return selectMaps(SpringUtil.getBean(DataSource.class), sql);
    }

    /**
     * 执行select sql返回List
     * @param sql
     * @return
     */
    public static List<Map<String, Object>> selectMaps(DataSource dataSource, String sql){
        Connection connection = null;
        try {
            connection = DataSourceUtils.doGetConnection(dataSource);
            String ipPort = JdbcParseUtils.findIpPortByUrl(connection.getMetaData().getURL());
            log.debug("====>   selectMaps connection: {}", ipPort);
            log.debug("====>   Sql: {}", sql);
            List<Entity> mapList = DbUtil.use(dataSource).query(sql);
            if(mapList != null){
                List<Map<String, Object>> collect = mapList.stream().map(row -> {
                    //返回Map key转驼峰: bug--ID不能转id
                    //Map<String, Object> newMap = MapUtil.toCamelCaseMap(row);
                    Map<String, Object> newMap = new HashMap<>();
                    row.forEach((k,v) -> newMap.put(StrUtil.toCamelCase(k.toLowerCase()), v));
                    return newMap;
                }).collect(Collectors.toList());
                log.debug("====>   Total: {}", collect.size());
                return collect;
            }
        } catch (SQLException e) {
            log.error(e.getMessage(), e);
            throw new RuntimeException(e.getMessage());
        } finally {
            try {
                DataSourceUtils.doReleaseConnection(connection, dataSource);
            } catch (SQLException e) {
                log.error("fail to close connection !!!");
            }
        }
        List<Map<String, Object>> list = new ArrayList<>();
        return list;
    }

    public static Map<String, Object> selectOne(String sql){
        return selectOne(SpringUtil.getBean(DataSource.class), sql);
    }

    /**
     * 查询一条记录
     * @param sql
     * @return
     */
    public static Map<String, Object> selectOne(DataSource dataSource, String sql){
        Connection connection = null;
        try {
            connection = DataSourceUtils.doGetConnection(dataSource);
            String ipPort = JdbcParseUtils.findIpPortByUrl(connection.getMetaData().getURL());
            log.debug("====>   selectOne connection: {}", ipPort);
            log.debug("====>   Sql: {}", sql);
            Entity map = DbUtil.use(dataSource).queryOne(sql);
            if(map!=null){
                //返回Map key转驼峰: bug--ID不能转id
                //Map<String, Object> newMap = MapUtil.toCamelCaseMap(row);
                Map<String, Object> newMap = new HashMap<>();
                map.forEach((k,v) -> newMap.put(StrUtil.toCamelCase(k.toLowerCase()), v));
                return newMap;
            }
        } catch (SQLException e) {
            //未查询到结果不抛出异常，返回null既可
            log.warn(e.getMessage());
        } finally {
            try {
                DataSourceUtils.doReleaseConnection(connection, dataSource);
            } catch (SQLException e) {
                log.error("fail to close connection !!!");
            }
        }
        return null;
    }

    public static Long count(String countSql){
        return count(SpringUtil.getBean(DataSource.class), countSql);
    }

    /**
     * 执行统计SQL
     * @param dataSource
     * @param countSql
     * @return
     */
    public static Long count(DataSource dataSource, String countSql){
        Connection connection = null;
        try {
            connection = DataSourceUtils.doGetConnection(dataSource);
            String ipPort = JdbcParseUtils.findIpPortByUrl(connection.getMetaData().getURL());
            log.debug("====>   count connection: {}", ipPort);
            log.debug("====>   Sql: {}", countSql);
            Entity map = DbUtil.use(dataSource).queryOne(countSql);
            if (map != null) {
                Iterator<Object> i = map.values().iterator();
                if (i.hasNext()) {
                    Object genkey = i.next();
                    if (genkey != null) {
                        Long num = Long.valueOf(genkey.toString());
                        log.debug("<====   Total: {}", num);
                        return num;
                    }
                }
            }
        } catch (SQLException e) {
            log.error(e.getMessage(), e);
            throw new RuntimeException(e.getMessage());
        } finally {
            try {
                DataSourceUtils.doReleaseConnection(connection, dataSource);
            } catch (SQLException e) {
                log.error("fail to close connection !!!");
            }
        }
        return 0L;
    }

    public static Page<Map<String, Object>> selectMapsPage(String sql, int pageNum, int pageSize){
        return selectMapsPage(SpringUtil.getBean(DataSource.class), sql, pageNum, pageSize);
    }

    /**
     * 分页查询
     * @param dataSource
     * @param sql
     * @param pageNum  当前记录数
     * @param pageSize   每页查询数量
     * @return
     */
    public static Page<Map<String, Object>> selectMapsPage(DataSource dataSource, String sql, int pageNum, int pageSize){
        DbType dbType = JdbcDbTypeUtil.getDbTypeOfDruid(dataSource);
        String countSql = PagerUtils.count(sql, dbType).replaceAll("\\s+"," ");
        Long totalNum = count(dataSource, countSql);
        if(totalNum != null && totalNum > 0){
            String limitSql = PagerUtils.limit(sql, dbType, pageNum-1, pageSize).replaceAll("\\s+"," ");
            Page<Map<String, Object>> page = new Page<>(pageNum, pageSize, totalNum);
            page.setRecords(selectMaps(dataSource, limitSql));
            return page;
        }
        return new Page<>();
    }

    public static int insert(String sql){
        return insert(SpringUtil.getBean(DataSource.class), sql);
    }

    /**
     * 执行insert into
     * @param sql
     * @return
     */
    public static int insert(DataSource dataSource, String sql){
        Connection connection = null;
        try {
            connection = DataSourceUtils.doGetConnection(dataSource);
            String ipPort = JdbcParseUtils.findIpPortByUrl(connection.getMetaData().getURL());
            log.debug("====>   insert connection: {}", ipPort);
            log.debug("====>   Sql: {}", sql);
            int num = SqlExecutor.execute(connection, sql);
            log.debug("<====   Total: {}", num);
            return num;
        } catch (SQLException e) {
            log.error(e.getMessage(), e);
            throw new RuntimeException(e.getMessage());
        } finally {
            try {
                DataSourceUtils.doReleaseConnection(connection, dataSource);
            } catch (SQLException e) {
                log.error("fail to close connection !!!");
            }
        }
    }

    public static int update(String sql){
        return update(SpringUtil.getBean(DataSource.class), sql);
    }

    /**
     * 执行update set
     * @param sql
     * @return
     */
    public static int update(DataSource dataSource, String sql){
        Connection connection = null;
        try {
            connection = DataSourceUtils.doGetConnection(dataSource);
            String ipPort = JdbcParseUtils.findIpPortByUrl(connection.getMetaData().getURL());
            log.debug("====>   update connection: {}", ipPort);
            log.debug("====>   Sql: {}", sql);
            int num = SqlExecutor.execute(connection, sql);
            log.debug("<====   Total: {}", num);
            return num;
        } catch (SQLException e) {
            log.error(e.getMessage(), e);
            throw new RuntimeException(e.getMessage());
        } finally {
            try {
                DataSourceUtils.doReleaseConnection(connection, dataSource);
            } catch (SQLException e) {
                log.error("fail to close connection !!!");
            }
        }
    }

    public static int delete(String sql){
        return delete(SpringUtil.getBean(DataSource.class), sql);
    }

    /**
     * 执行delete from table where xxx
     * @param sql
     * @return
     */
    public static int delete(DataSource dataSource, String sql){
        Connection connection = null;
        try {
            connection = DataSourceUtils.doGetConnection(dataSource);
            String ipPort = JdbcParseUtils.findIpPortByUrl(connection.getMetaData().getURL());
            log.debug("====>   delete connection: {}", ipPort);
            log.debug("====>   Sql: {}", sql);
            int num = SqlExecutor.execute(connection, sql);
            log.debug("<====   Total: {}", num);
            return num;
        } catch (SQLException e) {
            log.error(e.getMessage(), e);
            throw new RuntimeException(e.getMessage());
        } finally {
            try {
                DataSourceUtils.doReleaseConnection(connection, dataSource);
            } catch (SQLException e) {
                log.error("fail to close connection !!!");
            }
        }
    }
}
